package com.ruoyi.factory.qylock.service.impl;

import com.ruoyi.common.utils.StringUtils;
import com.ruoyi.factory.archive.mapper.ArchiveMapper;
import com.ruoyi.factory.qylock.service.EsignTempService;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;

import java.math.BigDecimal;
import java.sql.*;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @ClassName EsignTemServiceImpl
 * @Description: TODO 模板数据填充
 * @Author LiuJiaji
 * @Date 2023/6/9 0009 11:10
 **/
@Service
public class EsignTempServiceImpl implements EsignTempService {

    protected final Logger logger = LoggerFactory.getLogger(EsignTempServiceImpl.class);

    @Value("${erp.oracle.url}")
    private String oracleUrl;
    @Value("${erp.oracle.username}")
    private String oracleUserName;
    @Value("${erp.oracle.password}")
    private String oraclePassword;

    @Autowired
    private ArchiveMapper archiveMapper;

    @Override
    public String handleTempPramOne(String pathstr, Map<String,String> pmap){
        JSONObject data = JSONObject.fromObject(pathstr);
        JSONObject paramry = new JSONObject();
        String filename = data.optString("filename");
        String sn = data.optString("sn");
        String currUserPhone = data.optString("currUserPhone");
        JSONArray jsonArray = data.optJSONArray("oids");
        String companyname = archiveMapper.selectCompanyByPkid(data.optString("pk_org"));

        String oid = (String)jsonArray.get(0);
        //根据oid查询信息
        String sql="";
        // 定义oracle数据库的驱动的类
        String driverClass = "oracle.jdbc.OracleDriver";
        // 定义连接的oracle的url
        String url = oracleUrl;
        // 用户名
        String username = oracleUserName;
        // 密码
        String password = oraclePassword;

        // 定义Connection连接
        Connection conn = null;
        // 定义数据库的sql执行对象
        Statement stmt = null;

        // 定义查询结果的ResultSet对象
        ResultSet rs = null;

        PreparedStatement pst = null;

        ResultSet rst = null;
        List<Map<String,String>> reList = new ArrayList<>();
        try {
            // 注册数据库的驱动程序
            Class.forName(driverClass);
            DriverManager.setLoginTimeout(60);
            // 获得数据库的连接
            conn = DriverManager.getConnection(url, username, password);
            // 创建sql执行对象
            stmt = conn.createStatement();

            sql = "select distinct salh.vbillcode htbm, salh.subscribedate qdrq, salh.vdef10 qsdd, salh.ntotalastnum zsl, salh.ntotalorigmny zje,\n" +
                    "salh.invallidate zzrq, salh.vdef5 fkfs,\n" +
                    "salb.crowno hh, salb.nastnum sl, salb.nqtorigtaxprice hsdj, salb.norigtaxmny jshj, salb.ntaxmny je, salb.vbdef10 khcpmc, salb.vbdef9 khcpgg,\n" +
                    "cush.name dwmc, cush.legalbody fr, cush.tel1 dhh,cush.fax1 cz,  cush.taxpayerid sh,\n" +
                    "addh.detailinfo dwdz, banh.accnum zh, banh.combineaccname khyh,\n" +
                    "math.name cpmc, math.materialspec cpgg, defc.name bzw, meah.name dw,\n" +
                    "incb.paymentday zqts,incch.name xsxymc, defcc.name yslx\n" +
                    "from \n" +
                    "ct_sale salh \n" +
                    "left join CT_SALE_B salb on salb.pk_ct_sale = salh.pk_ct_sale and salb.dr=0\n" +
                    "left join bd_customer cush on cush.PK_CUSTOMER = salh.pk_customer\n" +
                    "left join BD_MATERIAL_V math on math.pk_source = salb.pk_material\n" +
                    "left join bd_defdoc defc on defc.pk_defdoc = salb.vfree1\n" +
                    "left join bd_measdoc meah on meah.pk_measdoc = salb.castunitid\n" +
                    "left join bd_defdoc defcc on defcc.pk_defdoc = salh.vdef3\n" +
                    "left join BD_INCOME inch on inch.pk_income = salh.pk_payterm and inch.dr=0\n" +
                    "left join BD_INCOMECH incb on incb.pk_payment = inch.pk_income and incb.dr=0 and incb.prepayment ='N'\n" +
                    "left join bd_incomeperiod incch on incch.pk_incomeperiod = incb.pk_incomeperiod and incch.dr=0\n" +
                    "left join bd_custbank custb on custb.pk_cust = cush.pk_customer and custb.dr=0 and custb.isdefault = 'Y'\n" +
                    "left join bd_bankaccbas banh on banh.pk_bankaccbas = custb.pk_bankaccbas and banh.dr=0\n" +
                    "left join bd_address addh on addh.pk_address = cush.corpaddress  and addh.dr=0\n" +
                    "where salh.pk_ct_sale = ? and salh.dr=0  and salh.BLATEST='Y' ORDER BY salb.crowno asc";
            pst = conn.prepareStatement(sql);
            pst.setObject(1,oid);
            rst = pst.executeQuery();
            int i = 0;
            logger.info("sql值: "+sql);
            //封装 处理返回结果
            while (rst.next()) {
                Map<String,String> resmap = new HashMap<>();
                String htbm = rst.getString("htbm");
                String qdrq = rst.getString("qdrq");
                String qsdd = rst.getString("qsdd");
                String zsl = rst.getString("zsl");
                String zje = rst.getString("zje");
                String zzrq = rst.getString("zzrq");
                String sl = rst.getString("sl");
                String hsdj = rst.getString("hsdj");
                String je = rst.getString("je");
                String dwmc = rst.getString("dwmc");
                String fr = rst.getString("fr");
                String dhh = rst.getString("dhh");
                String cz = rst.getString("cz");
                String sh = rst.getString("sh");
                String dwdz = rst.getString("dwdz");
                String zh = rst.getString("zh");
                String khcpmc = rst.getString("khcpmc");
                String khcpgg = rst.getString("khcpgg");
                String cpmc = rst.getString("cpmc");
                String cpgg = rst.getString("cpgg");
                String fkfs = rst.getString("fkfs");
                if(StringUtils.isNotEmpty(khcpmc) && !"~".equals(khcpmc)){
                    cpmc = khcpmc;
                }
                if(StringUtils.isNotEmpty(khcpgg) && !"~".equals(khcpgg)){
                    cpgg = khcpgg;
                }
                String bzw = rst.getString("bzw");
                String dw = rst.getString("dw");
                String khyh = rst.getString("khyh");
                String jshj = rst.getString("jshj");
                String zqts = rst.getString("zqts");
                String xsxymc = rst.getString("xsxymc");
                String yslx = rst.getString("yslx");
                resmap.put("htbm",htbm);
                resmap.put("qdrq",qdrq);
                resmap.put("qsdd",qsdd);
                resmap.put("zsl",zsl);
                resmap.put("zje",zje);
                resmap.put("zzrq",zzrq);
                resmap.put("sl",sl);
                resmap.put("hsdj",hsdj);
                resmap.put("je",je);
                resmap.put("dwmc",dwmc);
                resmap.put("fr",fr);
                resmap.put("dhh",dhh);
                resmap.put("cz",cz);
                resmap.put("sh",sh);
                resmap.put("dwdz",dwdz);
                resmap.put("zh",zh);
                resmap.put("cpmc",cpmc);
                resmap.put("cpgg",cpgg);
                resmap.put("bzw",bzw);
                resmap.put("dw",dw);
                resmap.put("khyh",khyh);
                resmap.put("jshj",jshj);
                resmap.put("zqts",zqts);
                resmap.put("xsxymc",xsxymc);
                resmap.put("yslx",yslx);
                resmap.put("fkfs",fkfs);
                reList.add(resmap);
            }

            if(pst != null){
                pst.close();
            }
            if(rst != null){
                rst.close();
            }
            if (rs != null)
            {
                rs.close();
            }
            if (stmt != null)
            {
                stmt.close();
            }
            if (conn != null)
            {
                conn.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        paramry.put("subject", filename);
        paramry.put("sn", sn);
        paramry.put("categoryId", data.get("printTemplateID"));
        paramry.put("tenantName", companyname);
        paramry.put("creatorContact", currUserPhone);
        paramry.put("msgCode", 2);

        List<JSONObject> list2 = new ArrayList<>();
        JSONObject param2 = new JSONObject();
        param2.put("tenantType", "COMPANY");
        param2.put("tenantName", companyname);
        param2.put("serialNo", 1);
        param2.put("receiverName",currUserPhone);
        param2.put("contact", currUserPhone);
        list2.add(param2);
        paramry.put("signatories", list2);

        List<JSONObject> list3 = new ArrayList<>();
        JSONObject field1 = new JSONObject();
        JSONObject field2 = new JSONObject();
        JSONObject field3 = new JSONObject();
        JSONObject field4 = new JSONObject();
        JSONObject field5 = new JSONObject();

        JSONObject field19 = new JSONObject();
        JSONObject field20 = new JSONObject();
        JSONObject field21 = new JSONObject();
        JSONObject field22 = new JSONObject();
        JSONObject field23 = new JSONObject();
        JSONObject field24 = new JSONObject();
        JSONObject field25 = new JSONObject();
        JSONObject field26 = new JSONObject();
        JSONObject field27 = new JSONObject();
        JSONObject field28 = new JSONObject();
        JSONObject field29 = new JSONObject();
        JSONObject field30 = new JSONObject();
        JSONObject field31 = new JSONObject();

        if(reList.isEmpty()) logger.info("relist集合为空");
        //处理非表格数据
        Map<String, String> rep = reList.get(0);
        String htbm = rep.get("htbm");
        String qdrq = rep.get("qdrq");
        String qsdd = rep.get("qsdd");
        String zsl = rep.get("zsl");
        String zje = rep.get("zje");
        String zzrq = rep.get("zzrq");
        String sl = rep.get("sl");
        String hsdj = rep.get("hsdj");
        String je = rep.get("je");
        String dwmc = rep.get("dwmc");
        String fr = rep.get("fr");
        String dhh = rep.get("dhh");
        String cz = rep.get("cz");
        String sh = rep.get("sh");
        String dwdz  =rep.get("dwdz");
        String zh = rep.get("zh");
        String cpmc = rep.get("cpmc");
        String cpgg = rep.get("cpgg");
        String bzw  = rep.get("bzw");
        String dw = rep.get("dw");
        String khyh = rep.get("khyh");
        String jshj = rep.get("jshj");
        String zqts = rep.get("zqts");
        String xsxymc = rep.get("xsxymc");
        String yslx = rep.get("yslx");
        String fkfs = rep.get("fkfs");

        field1.put("name", "合同编码");
        field1.put("value", htbm);


        SimpleDateFormat sim = new SimpleDateFormat("yyyy-MM-dd");
        Date parse = null;
        Date parse2 = null;
        try {
            if(StringUtils.isNotEmpty(qdrq)){
                parse = sim.parse(qdrq);
                qdrq = sim.format(parse);
            }else{
                qdrq = "";
            }
            if(StringUtils.isNotEmpty(zzrq)){
                parse2 = sim.parse(zzrq);
                zzrq = sim.format(parse2);
            }else{
                zzrq = "";
            }
        } catch (ParseException e) {
            e.printStackTrace();
        }
        field2.put("name", "签订时间");
        field2.put("value", qdrq);
        field3.put("name", "单位名称");
        field3.put("value", dwmc);

        field5.put("name", "签订地点");
        field5.put("value", qsdd);
        //判断是否是 templates 参数 根据模板选择不同的表格并处理数据
        JSONArray templates = new JSONArray();
        String s = "";
        if(reList.size()==1){
            //1个产品
            s = pmap.get("01").trim();
        }else if(reList.size()==2){
            //2个产品
            s = pmap.get("02").trim();
        }else if(reList.size()==3){
            //3个产品
            s = pmap.get("03").trim();
        }else if(reList.size()>3){
            //暂时固定 多个产品
            s = pmap.get("04").trim();
        }
        templates.add(s);
        paramry.put("templates",templates);
        for (int i = 0; i < reList.size(); i++) {
            int j = i+1;
            String scpmc = "产品名称"+j;
            String scpgg = "型号"+j;
            String sdw = "单位"+j;
            String ssl = "数量"+j;
            String dj = "单价"+j;
            String sjshj = "金额"+j;
            String sbzw = "包装规格"+j;
            JSONObject field32 = new JSONObject();
            JSONObject field33 = new JSONObject();
            JSONObject field34 = new JSONObject();
            JSONObject field35 = new JSONObject();
            JSONObject field36 = new JSONObject();
            JSONObject field37 = new JSONObject();
            JSONObject field38 = new JSONObject();
            Map<String, String> rep1 = reList.get(i);
            String cpmc2 = rep1.get("cpmc");
            String cpgg2 = rep1.get("cpgg");
            String dw2 = rep1.get("dw");
            String sl2 = rep1.get("sl");
            String jshj2 = rep1.get("jshj");
            String bzw2 = rep1.get("bzw");
            String dj2 = rep1.get("hsdj");
            field32.put("name", scpmc);
            field32.put("value", cpmc2);
            field33.put("name", scpgg);
            field33.put("value", cpgg2);
            field34.put("name", sdw);
            field34.put("value", dw2);
            field35.put("name", ssl);
            sl2 = new BigDecimal(sl2).setScale(3,BigDecimal.ROUND_HALF_UP).toString();
            field35.put("value", sl2);
            field36.put("name", sjshj);
            jshj2 = new BigDecimal(jshj2).setScale(2,BigDecimal.ROUND_HALF_UP).toString();
            field36.put("value", jshj2);
            field37.put("name", sbzw);
            field37.put("value", bzw2);
            field38.put("name", dj);
            dj2 = new BigDecimal(dj2).setScale(2,BigDecimal.ROUND_HALF_UP).toString();
            field38.put("value", dj2);
            list3.add(field32);
            list3.add(field33);
            list3.add(field34);
            list3.add(field35);
            list3.add(field36);
            list3.add(field37);
            list3.add(field38);
        }

        field19.put("name", "合计数量");
        zsl = new BigDecimal(zsl).setScale(3,BigDecimal.ROUND_HALF_UP).toString();
        field19.put("value", zsl);
        field20.put("name", "合计金额");
        zje = new BigDecimal(zje).setScale(2,BigDecimal.ROUND_HALF_UP).toString();
        field20.put("value", zje);

        JSONObject field40 = new JSONObject();
        JSONObject field41 = new JSONObject();
        field40.put("name", "付款方式");
        if("销售开票日期".equals(xsxymc) && !"0".equals(zqts)){
            field40.put("value", "开票日起"+zqts+"天内付款。");
        }else{
            field40.put("value","款到发货。");
        }
        //锦州豪润达取vdef5
        if(pmap.get("name").contains("2001Z3BZ01")){
            if(StringUtils.isNotEmpty(fkfs) && !fkfs.equals("~")){
                field40.put("value",fkfs);
            }
        }
        field41.put("name", "运输方式");
        field41.put("value", yslx+"。");
        list3.add(field40);
        list3.add(field41);
        field21.put("name", "合同有效期");
        field21.put("value", qdrq+" 至 "+zzrq);
        field22.put("name", "单位名称");
        field22.put("value", dwmc);
        field23.put("name", "单位地址");
        field23.put("value", dwdz);
        field24.put("name", "法人代表");
        field24.put("value", fr);
        field27.put("name", "电话");
        field27.put("value", dhh);
        field28.put("name", "传真");
        field28.put("value", cz);
        field29.put("name", "开户银行");
        field29.put("value", khyh);
        field30.put("name", "银行账号");
        field30.put("value", zh);
        if(StringUtils.isNotEmpty(sh) && !sh.equals("~")){
            field31.put("name", "税号");
            field31.put("value", sh);
            list3.add(field31);
        }
        list3.add(field1);
        list3.add(field2);
        list3.add(field3);
        list3.add(field4);
        list3.add(field5);

        list3.add(field19);
        list3.add(field20);
        list3.add(field21);
        list3.add(field22);
        list3.add(field23);
        list3.add(field24);
        list3.add(field25);
        list3.add(field26);
        list3.add(field27);
        list3.add(field28);
        list3.add(field29);
        list3.add(field30);

        paramry.put("documentParams", list3);
        String params = paramry.toString();
        return params;
    }

    @Override
    public String handleTempPramTwo(String pathstr){

        JSONObject data = JSONObject.fromObject(pathstr);
        JSONObject paramry = new JSONObject();
        String filename = data.optString("filename");
        String sn = data.optString("sn");
        String currUserPhone = data.optString("currUserPhone");
        JSONArray jsonArray = data.optJSONArray("oids");
        String oid = (String)jsonArray.get(0);


        paramry.put("subject", filename);
        paramry.put("sn", sn);
        paramry.put("categoryId", data.get("printTemplateID"));
        paramry.put("tenantName", "新乡市瑞丰新材料股份有限公司");
        paramry.put("creatorContact", currUserPhone);

        List<JSONObject> list2 = new ArrayList<>();
        JSONObject param2 = new JSONObject();
        param2.put("tenantType", "COMPANY");
        param2.put("tenantName", "新乡市瑞丰新材料股份有限公司");
        param2.put("serialNo", 1);
        param2.put("receiverName",currUserPhone);
        param2.put("contact", currUserPhone);
        list2.add(param2);
        paramry.put("signatories", list2);

        String params = paramry.toString();
        return params;
    }

    @Override
    public String handleTempPramThree(String pathstr, Map<String,String> pmap){

        JSONObject data = JSONObject.fromObject(pathstr);
        JSONObject paramry = new JSONObject();
        String filename = data.optString("filename");
        String sn = data.optString("sn");
        String currUserPhone = data.optString("currUserPhone");
        JSONArray jsonArray = data.optJSONArray("oids");
        String companyname = archiveMapper.selectCompanyByPkid(data.optString("pk_org"));

        String oid = (String)jsonArray.get(0);
        //根据oid查询信息
        String sql="";
        // 定义oracle数据库的驱动的类
        String driverClass = "oracle.jdbc.OracleDriver";
        // 定义连接的oracle的url
        String url = oracleUrl;
        // 用户名
        String username = oracleUserName;
        // 密码
        String password = oraclePassword;

        // 定义Connection连接
        Connection conn = null;
        // 定义数据库的sql执行对象
        Statement stmt = null;

        // 定义查询结果的ResultSet对象
        ResultSet rs = null;

        PreparedStatement pst = null;

        ResultSet rst = null;
        List<Map<String,String>> reList = new ArrayList<>();
        try {
            // 注册数据库的驱动程序
            Class.forName(driverClass);
            DriverManager.setLoginTimeout(60);
            // 获得数据库的连接
            conn = DriverManager.getConnection(url, username, password);
            // 创建sql执行对象
            stmt = conn.createStatement();

            sql = "select distinct salh.vbillcode htbm, salh.subscribedate qdrq, salh.vdef10 qsdd, salh.ntotalastnum zsl, salh.ntotalorigmny zje,\n" +
                    "salh.invallidate zzrq, salh.vdef5 fkfs, salh.valdate sxrq, salh.vdef10 qsd,\n" +
                    "salb.crowno hh, salb.nastnum sl, salb.nqtorigtaxprice hsdj, salb.norigtaxmny jshj, salb.ntaxmny je, salb.vbdef10 khcpmc, salb.vbdef9 khcpgg, salb.norigmny bhsje,\n" +
                    "cush.name dwmc, cush.legalbody fr, cush.tel1 dhh,cush.fax1 cz,  cush.taxpayerid sh,\n" +
                    "addh.detailinfo dwdz, banh.accnum zh, banh.combineaccname khyh,\n" +
                    "math.name cpmc, math.materialspec cpgg, defc.name bzw, meah.name dw, meah2.name khdw, bcut.measrate meas,\n" +
                    "bcut.def1 khgg, salb.vbdef9 khgg2,\n" +
                    "incb.paymentday zqts,incch.name xsxymc, defcc.name yslx\n" +
                    "from \n" +
                    "ct_sale salh \n" +
                    "left join CT_SALE_B salb on salb.pk_ct_sale = salh.pk_ct_sale and salb.dr=0\n" +
                    "left join bd_customer cush on cush.PK_CUSTOMER = salh.pk_customer\n" +
                    "left join BD_MATERIAL_V math on math.pk_source = salb.pk_material\n" +
                    "left join bd_defdoc defc on defc.pk_defdoc = salb.vfree1\n" +
                    "left join bd_measdoc meah on meah.pk_measdoc = salb.castunitid and meah.dr=0\n" +
                    "left join bd_custmaterial bcut on bcut.pk_custmaterial = salb.ccustmaterialid and bcut.dr=0\n" +
                    "left join bd_measdoc meah2 on meah2.pk_measdoc = bcut.cmmeasdoc and meah2.dr=0\n" +
                    "left join bd_defdoc defcc on defcc.pk_defdoc = salh.vdef3\n" +
                    "left join BD_INCOME inch on inch.pk_income = salh.pk_payterm and inch.dr=0\n" +
                    "left join BD_INCOMECH incb on incb.pk_payment = inch.pk_income and incb.dr=0 and incb.prepayment ='N'\n" +
                    "left join bd_incomeperiod incch on incch.pk_incomeperiod = incb.pk_incomeperiod and incch.dr=0\n" +
                    "left join bd_custbank custb on custb.pk_cust = cush.pk_customer and custb.dr=0 and custb.isdefault = 'Y'\n" +
                    "left join bd_bankaccbas banh on banh.pk_bankaccbas = custb.pk_bankaccbas and banh.dr=0\n" +
                    "left join bd_address addh on addh.pk_address = cush.corpaddress  and addh.dr=0\n" +
                    "where salh.pk_ct_sale = ? and salh.dr=0 and salh.BLATEST='Y' ORDER BY salb.crowno asc";
            pst = conn.prepareStatement(sql);
            pst.setObject(1,oid);
            rst = pst.executeQuery();
            int i = 0;
            logger.info("sql值: "+sql);
            //封装 处理返回结果
            while (rst.next()) {
                Map<String,String> resmap = new HashMap<>();
                String htbm = rst.getString("htbm");
                //生效时间/签订时间
//                String qdrq = rst.getString("qdrq");
                String qdrq = rst.getString("sxrq");
                //签订地点
//                String qsdd = rst.getString("qsdd");
                String qsdd = rst.getString("qsd");
                String zsl = rst.getString("zsl");
                String zje = rst.getString("zje");
                String zzrq = rst.getString("zzrq");
                String sl = rst.getString("sl");
                String hsdj = rst.getString("hsdj");
                String je = rst.getString("je");
                String dwmc = rst.getString("dwmc");
                String fr = rst.getString("fr");
                String dhh = rst.getString("dhh");
                String cz = rst.getString("cz");
                String sh = rst.getString("sh");
                String dwdz = rst.getString("dwdz");
                String zh = rst.getString("zh");
                String khcpmc = rst.getString("khcpmc");
                String khcpgg = rst.getString("khcpgg");
                String cpmc = rst.getString("cpmc");
                String cpgg = rst.getString("cpgg");
                String fkfs = rst.getString("fkfs");
                String dw = rst.getString("dw");
                String khdw = rst.getString("khdw");
                String meas = rst.getString("meas");
                String bhsje = rst.getString("bhsje");
                if(StringUtils.isNotEmpty(khcpmc) && !"~".equals(khcpmc)){
                    cpmc = khcpmc;
                }
                String khgg = rst.getString("khgg");
//                if(StringUtils.isNotEmpty(khcpgg) && !"~".equals(khcpgg)){
//                    cpgg = khcpgg;
//                }
                if(StringUtils.isNotEmpty(khgg) && !"~".equals(khgg)){
                    khcpgg = rst.getString("khcpgg");
                }
                String bzw = rst.getString("bzw");
                //单位 优先选取客户单位
                if(StringUtils.isNotEmpty(khdw) && !"~".equals(khdw)){
                    dw = khdw;
                    //重新计算数量和单价
                    String[] split = meas.split("/");
                    BigDecimal meass = new BigDecimal(split[0]).divide(new BigDecimal(split[1]));
                    //重新计算数量
                    sl = new BigDecimal(sl).divide(meass,0).toString();
                    //重新计算单价
                    hsdj = new BigDecimal(hsdj).multiply(meass).toString();
                }

                String khyh = rst.getString("khyh");
                String jshj = rst.getString("jshj");
                String zqts = rst.getString("zqts");
                String xsxymc = rst.getString("xsxymc");
                String yslx = rst.getString("yslx");
                resmap.put("htbm",htbm);
                resmap.put("qdrq",qdrq);
                resmap.put("qsdd",qsdd);
                resmap.put("zsl",zsl);
                resmap.put("zje",zje);
                resmap.put("zzrq",zzrq);
                resmap.put("sl",sl);
                resmap.put("hsdj",hsdj);
                resmap.put("je",je);
                resmap.put("dwmc",dwmc);
                resmap.put("fr",fr);
                resmap.put("dhh",dhh);
                resmap.put("cz",cz);
                resmap.put("sh",sh);
                resmap.put("dwdz",dwdz);
                resmap.put("zh",zh);
                resmap.put("cpmc",cpmc);
                resmap.put("cpgg",khcpgg);
                resmap.put("bzw",bzw);
                resmap.put("dw",dw);
                resmap.put("khyh",khyh);
                resmap.put("jshj",jshj);
                resmap.put("zqts",zqts);
                resmap.put("xsxymc",xsxymc);
                resmap.put("yslx",yslx);
                resmap.put("fkfs",fkfs);
                resmap.put("bhsje",bhsje);
                reList.add(resmap);
            }

            if(pst != null){
                pst.close();
            }
            if(rst != null){
                rst.close();
            }
            if (rs != null)
            {
                rs.close();
            }
            if (stmt != null)
            {
                stmt.close();
            }
            if (conn != null)
            {
                conn.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        paramry.put("subject", filename);
        paramry.put("sn", sn);
        paramry.put("categoryId", data.get("printTemplateID"));
        paramry.put("tenantName", companyname);
        paramry.put("creatorContact", currUserPhone);
        paramry.put("msgCode", 2);

        List<JSONObject> list2 = new ArrayList<>();
        JSONObject param2 = new JSONObject();
        param2.put("tenantType", "COMPANY");
        param2.put("tenantName", companyname);
        param2.put("serialNo", 1);
        param2.put("receiverName",currUserPhone);
        param2.put("contact", currUserPhone);
        list2.add(param2);
        paramry.put("signatories", list2);

        List<JSONObject> list3 = new ArrayList<>();
        JSONObject field1 = new JSONObject();
        JSONObject field2 = new JSONObject();
        JSONObject field3 = new JSONObject();
        JSONObject field4 = new JSONObject();
        JSONObject field5 = new JSONObject();

        JSONObject field19 = new JSONObject();
        JSONObject field20 = new JSONObject();
        JSONObject field21 = new JSONObject();
        JSONObject field22 = new JSONObject();
        JSONObject field23 = new JSONObject();
        JSONObject field24 = new JSONObject();
        JSONObject field25 = new JSONObject();
        JSONObject field26 = new JSONObject();
        JSONObject field27 = new JSONObject();
        JSONObject field28 = new JSONObject();
        JSONObject field29 = new JSONObject();
        JSONObject field30 = new JSONObject();
        JSONObject field31 = new JSONObject();

        if(reList.isEmpty()) logger.info("relist集合为空");
        //处理非表格数据
        Map<String, String> rep = reList.get(0);
        String htbm = rep.get("htbm");
        String qdrq = rep.get("qdrq");
        String qsdd = rep.get("qsdd");
        String zsl = rep.get("zsl");
        String zje = rep.get("zje");
        String zzrq = rep.get("zzrq");
        String sl = rep.get("sl");
        String hsdj = rep.get("hsdj");
        String je = rep.get("je");
        String dwmc = rep.get("dwmc");
        String fr = rep.get("fr");
        String dhh = rep.get("dhh");
        String cz = rep.get("cz");
        String sh = rep.get("sh");
        String dwdz  =rep.get("dwdz");
        String zh = rep.get("zh");
        String cpmc = rep.get("cpmc");
        String cpgg = rep.get("cpgg");
        String bzw  = rep.get("bzw");
        String dw = rep.get("dw");
        String khyh = rep.get("khyh");
        String jshj = rep.get("jshj");
        String zqts = rep.get("zqts");
        String xsxymc = rep.get("xsxymc");
        String yslx = rep.get("yslx");
        String fkfs = rep.get("fkfs");

        field1.put("name", "合同编码");
        field1.put("value", htbm);


        SimpleDateFormat sim = new SimpleDateFormat("yyyy-MM-dd");
        Date parse = null;
        Date parse2 = null;
        try {
            if(StringUtils.isNotEmpty(qdrq)){
                parse = sim.parse(qdrq);
                qdrq = sim.format(parse);
            }else{
                qdrq = "";
            }
            if(StringUtils.isNotEmpty(zzrq)){
                parse2 = sim.parse(zzrq);
                zzrq = sim.format(parse2);
            }else{
                zzrq = "";
            }
        } catch (ParseException e) {
            e.printStackTrace();
        }
        field2.put("name", "签订时间");
        field2.put("value", qdrq);
        field3.put("name", "单位名称");
        field3.put("value", dwmc);

        field5.put("name", "签订地点");
        field5.put("value", qsdd);
        //判断是否是 templates 参数 根据模板选择不同的表格并处理数据
        JSONArray templates = new JSONArray();
        String s = "";
        if(reList.size()==1){
            //1个产品
            s = pmap.get("01").trim();
        }else if(reList.size()==2){
            //2个产品
            s = pmap.get("02").trim();
        }else if(reList.size()==3){
            //3个产品
            s = pmap.get("03").trim();
        }else if(reList.size()>3){
            //暂时固定 多个产品
            s = pmap.get("04").trim();
        }
        templates.add(s);
        paramry.put("templates",templates);
        BigDecimal total = new BigDecimal("0");
        //不含税总金额
        BigDecimal bhszje = new BigDecimal("0");
        for (int i = 0; i < reList.size(); i++) {
            int j = i+1;
            String scpmc = "产品名称"+j;
            String scpgg = "型号"+j;
            String sdw = "单位"+j;
            String ssl = "数量"+j;
            String dj = "单价"+j;
            String sjshj = "金额"+j;
            String sbzw = "包装规格"+j;
            String bhsje = "不含税金额"+j;
            JSONObject field32 = new JSONObject();
            JSONObject field33 = new JSONObject();
            JSONObject field34 = new JSONObject();
            JSONObject field35 = new JSONObject();
            JSONObject field36 = new JSONObject();
            JSONObject field37 = new JSONObject();
            JSONObject field38 = new JSONObject();
            JSONObject field39 = new JSONObject();
            Map<String, String> rep1 = reList.get(i);
            String cpmc2 = rep1.get("cpmc");
            String cpgg2 = rep1.get("cpgg");
            String dw2 = rep1.get("dw");
            String sl2 = rep1.get("sl");
            String jshj2 = rep1.get("jshj");
            String bzw2 = rep1.get("bzw");
            String dj2 = rep1.get("hsdj");
            field32.put("name", scpmc);
            field32.put("value", cpmc2);
            field33.put("name", scpgg);
            field33.put("value", cpgg2);
            field34.put("name", sdw);
            field34.put("value", dw2);
            field35.put("name", ssl);
            sl2 = new BigDecimal(sl2).setScale(3,BigDecimal.ROUND_HALF_UP).toString();
            field35.put("value", sl2);
            field36.put("name", sjshj);
            jshj2 = new BigDecimal(jshj2).setScale(2,BigDecimal.ROUND_HALF_UP).toString();
            field36.put("value", jshj2);
            field37.put("name", sbzw);
            field37.put("value", bzw2);
            field38.put("name", dj);
            dj2 = new BigDecimal(dj2).setScale(2,BigDecimal.ROUND_HALF_UP).toString();
            field38.put("value", dj2);
            field39.put("name", bhsje);
            String bhsje2 = new BigDecimal(rep1.get("bhsje")).setScale(2,BigDecimal.ROUND_HALF_UP).toString();
            field39.put("value", bhsje2);
            bhszje = bhszje.add(new BigDecimal(bhsje2));
            list3.add(field32);
            list3.add(field33);
            list3.add(field34);
            list3.add(field35);
            list3.add(field36);
            list3.add(field37);
            list3.add(field38);
            list3.add(field39);
            total = total.add(new BigDecimal(sl2));
        }

        JSONObject field32 = new JSONObject();

        field19.put("name", "合计数量");
//        zsl = new BigDecimal(zsl).setScale(3,BigDecimal.ROUND_HALF_UP).toString();
        zsl = total.setScale(3,BigDecimal.ROUND_HALF_UP).toString();
        field19.put("value", zsl);
        field20.put("name", "合计金额");
        zje = new BigDecimal(zje).setScale(2,BigDecimal.ROUND_HALF_UP).toString();
        field20.put("value", zje);

        JSONObject field40 = new JSONObject();
        JSONObject field41 = new JSONObject();
        field40.put("name", "付款方式");
        if("销售开票日期".equals(xsxymc) && !"0".equals(zqts)){
            field40.put("value", "开票日起"+zqts+"天内付款。");
        }else{
            field40.put("value","款到发货。");
        }
        //锦州豪润达取vdef5
        if(pmap.get("name").contains("2001Z3BZ01")){
            if(StringUtils.isNotEmpty(fkfs) && !fkfs.equals("~")){
                field40.put("value",fkfs);
            }
        }
        field41.put("name", "运输方式");
        field41.put("value", yslx+"。");
        list3.add(field40);
        list3.add(field41);
        field21.put("name", "合同有效期");
        field21.put("value", qdrq+" 至 "+zzrq);
        field22.put("name", "单位名称");
        field22.put("value", dwmc);
        field23.put("name", "单位地址");
        field23.put("value", dwdz);
        field24.put("name", "法人代表");
        field24.put("value", fr);
        field27.put("name", "电话");
        field27.put("value", dhh);
        field28.put("name", "传真");
        field28.put("value", cz);
        field29.put("name", "开户银行");
        field29.put("value", khyh);
        field30.put("name", "银行账号");
        field30.put("value", zh);
        if(StringUtils.isNotEmpty(sh) && !sh.equals("~")){
            field31.put("name", "税号");
            field31.put("value", sh);
            list3.add(field31);
        }
        field32.put("name", "不含税总金额");
        field32.put("value", bhszje);
        list3.add(field1);
        list3.add(field2);
        list3.add(field3);
        list3.add(field4);
        list3.add(field5);

        list3.add(field19);
        list3.add(field20);
        list3.add(field21);
        list3.add(field22);
        list3.add(field23);
        list3.add(field24);
        list3.add(field25);
        list3.add(field26);
        list3.add(field27);
        list3.add(field28);
        list3.add(field29);
        list3.add(field30);
        list3.add(field32);

        paramry.put("documentParams", list3);
        String params = paramry.toString();
        return params;
    }
}
